package com.gzx.plugin.tjzy.modular.work.service.impl;

import java.io.File;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.collect.Lists;
import com.gzx.plugin.tjzy.modular.work.bo.UserPayment;

public class PaymentsExcelReader {
	public static Object toValue(Cell cell) {
		switch (cell.getCellType()) {
		case STRING:
			return cell.getStringCellValue();
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				return formatDateCell(cell);
			} else {
				return formatNumberCell(cell);
			}
		case BOOLEAN:
			return cell.getBooleanCellValue();
		case FORMULA:
			return cell.getCellFormula();
		default:
			System.out.print("\t");
		}
		return null;
	}

	/**
	 * 格式化日期类型的单元格
	 * 
	 * @param cell 日期类型的单元格
	 * @return 格式化后的日期字符串
	 */
	private static String formatDateCell(Cell cell) {
		Date date = cell.getDateCellValue();
		// 默认日期格式
		return new SimpleDateFormat("yyyy-MM-dd").format(date);
	}

	/**
	 * 格式化普通数字类型的单元格
	 * 
	 * @param cell 数字类型的单元格
	 * @return 格式化后的数字字符串
	 */
	private static String formatNumberCell(Cell cell) {
		double numericValue = cell.getNumericCellValue();

		// 检查是否为整数
		if (numericValue == Math.floor(numericValue) && !Double.isInfinite(numericValue)) {
			return String.format("%.0f", numericValue);
		}

		// 获取单元格的格式
		CellStyle style = cell.getCellStyle();
		String formatString = style.getDataFormatString();

		// 如果是百分比格式
		if (formatString != null && formatString.contains("%")) {
			return new DecimalFormat("0.00%").format(numericValue);
		}

		// 默认使用两位小数
		return new DecimalFormat("#,##0.00").format(numericValue);
	}

	public static List<UserPayment> loadPayments(String rootDir) throws Exception {
		List<UserPayment> payments = Lists.newArrayList();
		File file = new File(rootDir + "/维信回款250728.xlsx");
		if (!file.exists()) {
			return payments;
		}

		Workbook workbook = new XSSFWorkbook(file);
		Sheet sheet = workbook.getSheetAt(0);
		for (int i = 1; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String name = (String) toValue(row.getCell(2));
			String businessId = "" + toValue(row.getCell(5));
			String date = (String) toValue(row.getCell(6));
			String amount = (String) toValue(row.getCell(11));
			UserPayment userPayment = new UserPayment();
			userPayment.setName(name);
			userPayment.setBusinessId(businessId);
			userPayment.setDate(date);
			userPayment.setAmount(amount);
			payments.add(userPayment);
		}
		workbook.close();
		return payments;
	}
}
